﻿using System;

using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;
using System.IO;
using System.Data;

namespace ProjetoFase2
{
    class DatabaseConnector
    {
        private string connectionString;
        private string username;
        private string password;

        public DatabaseConnector(string username, string password)
        {
            connectionString = @"Data Source=\My Documents\"+username+".sdf;"+
                    "SSCE:Database Password="+password+";";
            this.username = username;
            this.password = password;
        }

        public bool existsDatabase()
        {
            if (File.Exists("\\My Documents\\" + username + ".sdf"))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public void createDatabase()
        {
            if (File.Exists("\\My Documents\\"+username+".sdf"))
            {
                File.Delete("\\My Documents\\"+username+".sdf");
            }

            if (!File.Exists("\\My Documents\\" + username + ".sdf"))
            {
                SqlCeEngine engine = new SqlCeEngine(@"Data Source=\My Documents\" + username + 
                    ".sdf;Password="+password+";");
                engine.CreateDatabase();
                engine.Dispose();
            }
        }

        public void createTableTechnician()
        {
            SqlCeConnection con = new SqlCeConnection(connectionString);
            con.Open();
            SqlCeCommand comm = con.CreateCommand();
            comm.CommandText = "create table technician (id nvarchar(30) unique not null,"+
	                            "password nvarchar(30),"+
	                            "name nvarchar(50) not null,"+
	                            "district nvarchar(30) not null,"+
	                            "category int not null,"+
	                            "supervisor nvarchar(30),"+
	                            "primary key (id))";
            comm.ExecuteNonQuery();
            con.Close();
        }

        public void deleteResidence(string table_name, int resi_id)
        {
            SqlCeConnection con = new SqlCeConnection(connectionString);
            con.Open();
            SqlCeCommand comm = con.CreateCommand();
            comm.CommandText = "delete from " + table_name + " where resi_id=" + resi_id;
            comm.ExecuteNonQuery();
            con.Close();
        }

        public Residence selectResidence(string table_name, int resi_id)
        {
            SqlCeConnection con;
            Residence auxRes = null;

            con = new SqlCeConnection(connectionString);
            con.Open();
            SqlCeCommand command = con.CreateCommand();

            command.CommandText = "select * from " + table_name + " where resi_id="+resi_id;
            SqlCeDataReader dtReader = command.ExecuteReader();
            
            if (dtReader.Read())
                auxRes = new Residence(dtReader);

            dtReader.Close();
            con.Close();

            return auxRes;
        }

        public Technician selectTechnician(string id)
        {
            SqlCeConnection con;
            Technician auxTech = null;

            con = new SqlCeConnection(connectionString);
            con.Open();
            SqlCeCommand command = con.CreateCommand();

            command.CommandText = "select * from technician where id='" + id + "'";
            SqlCeDataReader dtReader = command.ExecuteReader();

            if (dtReader.Read())
                auxTech = new Technician(dtReader);

            dtReader.Close();
            con.Close();

            return auxTech;

        }

        public void getResidenceDataSet(string table_name, DataSet ds)
        {        
            SqlCeDataAdapter da;
            SqlCeCommandBuilder cb;
            SqlCeConnection cn;

            cn = new SqlCeConnection(connectionString);
            da = new SqlCeDataAdapter("select * from "+table_name, cn);
            cb = new SqlCeCommandBuilder(da);
            cn.Open();
            da.Fill(ds, "residence");
            cn.Close();
        }

        public void insertResidence(string table_name, Residence res)
        {
            SqlCeConnection con = new SqlCeConnection(connectionString);
            con.Open();
            SqlCeCommand comm = con.CreateCommand();

            if (res.Topology == null)
            {
                comm.CommandText = "insert into " + table_name + " (resi_id,address,district,client,"+
                    "category, visit_time) values (" +
                    res.Resi_id + ",'" + res.Address + "','" + res.District + "','" + res.Client +
                    "'," + res.Category + ",'" + res.Visit_time + "')";
            }
            else
            {
                comm.CommandText = "insert into " + table_name + " values (" +
                    res.Resi_id + ",'" + res.Address + "','" + res.District + "','" + res.Client +
                    "'," + res.Category + ",'" + res.Visit_time + "','" + res.Topology + "'," + 
                    res.Cooling + "," + res.Heating + 
                    "," + res.Isolation + ",'" + res.Coverage + "','" + res.Glasses + "',false)";
            }
                                
            comm.ExecuteNonQuery();
            con.Close();
        }

        public void insertTechnician(Technician tech)
        {
            SqlCeConnection con = new SqlCeConnection(connectionString);
            con.Open();
            SqlCeCommand comm = con.CreateCommand();

            if (tech.Supervisor == null)
            {
                comm.CommandText = "insert into technician (id,password,name,district,category) " +
                    "values ('" + tech.Id + "','" + tech.Password + "','" + tech.Name + "','" +
                    tech.District + "'," + tech.Category + ")";
            }
            else
            {
                comm.CommandText = "insert into technician (id,password,name,district,category,supervisor) " +
                    "values ('" + tech.Id + "','" + tech.Password + "','" + tech.Name + "','" + 
                    tech.District + "'," + tech.Category + 
                    ",'" + tech.Supervisor +"')";
            }

            comm.ExecuteNonQuery();
            con.Close();
        }

        public void createTableResidence(string table_name)
        {
            SqlCeConnection con = new SqlCeConnection(connectionString);
            con.Open();
            SqlCeCommand comm = con.CreateCommand();
            comm.CommandText = "create table "+table_name+" (resi_id int unique not null,"+
	                            "address nvarchar(70) not null,"+
	                            "district nvarchar(30) not null,"+
	                            "client nvarchar(50) not null,"+
	                            "category int not null default -1,"+
                                "visit_time nvarchar(12) not null,"+
	                            "topology nvarchar(5),"+
	                            "cooling int,"+
	                            "heating int,"+
	                            "isolation int,"+
	                            "coverage nvarchar (15),"+
	                            "glasses nvarchar (15),"+
                                "approved int not null default 0,"+
                                "primary key (resi_id))";
            comm.ExecuteNonQuery();
            con.Close();
        }

        public DataSet getVersionVectorDataSet(string table_name)
        {
            DataSet ds;
            SqlCeDataAdapter da;
            SqlCeCommandBuilder cb;
            SqlCeConnection cn;

            cn = new SqlCeConnection(connectionString);
            da = new SqlCeDataAdapter("select * from " + table_name, cn);
            cb = new SqlCeCommandBuilder(da);
            ds = new DataSet();
            cn.Open();
            da.Fill(ds, "version_vector");
            cn.Close();

            return ds;
        }

        public void createTableVersionVector(string table_name)
        {
            SqlCeConnection con = new SqlCeConnection(connectionString);
            con.Open();
            SqlCeCommand comm = con.CreateCommand();
            comm.CommandText = "create table "+ table_name+ " ("+
	                            "resi_id int not null,"+
	                            "server1 int not null default 0,"+
	                            "server2 int not null default 0,"+
	                            "primary key (resi_id))";
            comm.ExecuteNonQuery();
            con.Close();
        }


        public void insertVersionVector(string table_name, VersionVector auxVersion)
        {
            SqlCeConnection con = new SqlCeConnection(connectionString);
            con.Open();
            SqlCeCommand comm = con.CreateCommand();

            comm.CommandText = "select * from " + table_name + " where resi_id = " + auxVersion.Resi_id;
            SqlCeDataReader dtReader = comm.ExecuteReader();

            if (dtReader.Read())
            {
                comm.CommandText = "update " + table_name + " set server1=" + 
                    ((auxVersion.Server1 > dtReader.GetInt32(1)) ? auxVersion.Server1 : dtReader.GetInt32(1)) +
                    ", server2=" + 
                    ((auxVersion.Server2 > dtReader.GetInt32(2)) ? auxVersion.Server2 : dtReader.GetInt32(2)) + 
                    " where resi_id=" + auxVersion.Resi_id;
            }
            else
            {
                
                comm.CommandText = "insert into " + table_name + " (resi_id,server1,server2)" +
                    " values (" + auxVersion.Resi_id + "," + 
                    auxVersion.Server1 + "," +
                    auxVersion.Server2 + ")";
            }

            dtReader.Close();
            comm.ExecuteNonQuery();
            con.Close();            
        }

        public void deleteAllResidences(string table_name)
        {
            SqlCeConnection con = new SqlCeConnection(connectionString);
            con.Open();
            SqlCeCommand comm = con.CreateCommand();
            comm.CommandText = "delete from " + table_name;
            comm.ExecuteNonQuery();
            con.Close();
        }
    }
}
